{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. **缺失值处理**\n",
    "使用 Python 的 pandas 库来检测 CSV 文件中的缺失值\n",
    "这个代码会读取 weatherHistory.csv 文件并输出每一列中的缺失值数量。可以根据输出结果查看数据集中是否存在缺失值以及它们分布在哪些列。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失值检测结果：\n",
      "Formatted Date                0\n",
      "Summary                       0\n",
      "Precip Type                 517\n",
      "Temperature (C)               0\n",
      "Apparent Temperature (C)      0\n",
      "Humidity                      0\n",
      "Wind Speed (km/h)             0\n",
      "Wind Bearing (degrees)        0\n",
      "Visibility (km)               0\n",
      "Loud Cover                    0\n",
      "Pressure (millibars)          0\n",
      "Daily Summary                 0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 CSV 文件\n",
    "df = pd.read_csv('weatherHistory.csv')\n",
    "\n",
    "# 检查每列的缺失值\n",
    "missing_values = df.isnull().sum()\n",
    "\n",
    "# 输出每列缺失值的数量\n",
    "print(\"缺失值检测结果：\")\n",
    "print(missing_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可以看到Precip Type有517个数据为null，经检查都是rain，替换成rain\n",
    "使用 pandas 库中的 fillna() 函数将 Precip Type 列中的缺失值替换为 'rain'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在读取文件 weatherHistory.csv...\n",
      "文件读取成功！\n",
      "替换之前，Precip Type 列中有 517 个缺失值。\n",
      "替换之后，Precip Type 列中有 0 个缺失值。\n",
      "正在保存更改到文件 weatherHistory.csv...\n",
      "保存成功！\n",
      "文件已更新，缺失值已处理完毕。\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 CSV 文件\n",
    "print(\"正在读取文件 weatherHistory.csv...\")\n",
    "df = pd.read_csv('weatherHistory.csv')\n",
    "print(\"文件读取成功！\")\n",
    "\n",
    "# 检查 Precip Type 列中的缺失值数量\n",
    "missing_before = df['Precip Type'].isnull().sum()\n",
    "print(f\"替换之前，Precip Type 列中有 {missing_before} 个缺失值。\")\n",
    "\n",
    "# 将 Precip Type 列中的缺失值替换为 'rain'\n",
    "df['Precip Type'] = df['Precip Type'].fillna('rain')\n",
    "\n",
    "# 检查替换结果\n",
    "missing_after = df['Precip Type'].isnull().sum()\n",
    "print(f\"替换之后，Precip Type 列中有 {missing_after} 个缺失值。\")\n",
    "\n",
    "# 直接覆盖保存到原始 CSV 文件\n",
    "print(\"正在保存更改到文件 weatherHistory.csv...\")\n",
    "df.to_csv('weatherHistory.csv', index=False)\n",
    "print(\"保存成功！\")\n",
    "\n",
    "# 输出保存后的信息\n",
    "print(\"文件已更新，缺失值已处理完毕。\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. **异常值识别和处理**\n",
    "\n",
    "基于箱线图和统计特征,Loud Cover(云覆盖度)次列数据全部为0，推测是数据存在残缺或者问题，将此列删除。\n",
    "使用 pandas 库来删除 Loud Cover 列。\n",
    "使用 df.drop(columns=['Loud Cover'], inplace=True) 删除该列并在原数据上应用更改。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loud Cover 列中的值全部为 0，推测为异常数据，将该列删除。\n",
      "更新后的数据列：\n",
      "Index(['Formatted Date', 'Summary', 'Precip Type', 'Temperature (C)',\n",
      "       'Apparent Temperature (C)', 'Humidity', 'Wind Speed (km/h)',\n",
      "       'Wind Bearing (degrees)', 'Visibility (km)', 'Pressure (millibars)',\n",
      "       'Daily Summary'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 CSV 文件\n",
    "df = pd.read_csv('weatherHistory.csv')\n",
    "\n",
    "# 检查 Loud Cover 列是否全部为0\n",
    "if (df['Loud Cover'] == 0).all():\n",
    "    print(\"Loud Cover 列中的值全部为 0，推测为异常数据，将该列删除。\")\n",
    "    \n",
    "    # 删除 Loud Cover 列\n",
    "    df.drop(columns=['Loud Cover'], inplace=True)\n",
    "\n",
    "# 保存删除后的数据到原始文件\n",
    "df.to_csv('weatherHistory.csv', index=False)\n",
    "\n",
    "# 输出确认删除后的数据列名\n",
    "print(\"更新后的数据列：\")\n",
    "print(df.columns)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. **去重**\n",
    "\n",
    "Formatted Date（日期时间）不可能重复。需要检查其是否存在重复。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "发现 48 行重复的日期时间记录：\n",
      "                      Formatted Date        Summary Precip Type  \\\n",
      "8040   2010-08-02 00:00:00.000 +0200          Clear        rain   \n",
      "8041   2010-08-02 01:00:00.000 +0200          Clear        rain   \n",
      "8042   2010-08-02 02:00:00.000 +0200          Clear        rain   \n",
      "8043   2010-08-02 03:00:00.000 +0200          Clear        rain   \n",
      "8044   2010-08-02 04:00:00.000 +0200          Clear        rain   \n",
      "8045   2010-08-02 05:00:00.000 +0200          Clear        rain   \n",
      "8046   2010-08-02 06:00:00.000 +0200          Clear        rain   \n",
      "8047   2010-08-02 07:00:00.000 +0200          Clear        rain   \n",
      "8048   2010-08-02 08:00:00.000 +0200          Clear        rain   \n",
      "8049   2010-08-02 09:00:00.000 +0200          Clear        rain   \n",
      "8050   2010-08-02 10:00:00.000 +0200          Clear        rain   \n",
      "8051   2010-08-02 11:00:00.000 +0200          Clear        rain   \n",
      "8052   2010-08-02 12:00:00.000 +0200          Clear        rain   \n",
      "8053   2010-08-02 13:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8054   2010-08-02 14:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8055   2010-08-02 15:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8056   2010-08-02 16:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8057   2010-08-02 17:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8058   2010-08-02 18:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8059   2010-08-02 19:00:00.000 +0200          Clear        rain   \n",
      "8060   2010-08-02 20:00:00.000 +0200          Clear        rain   \n",
      "8061   2010-08-02 21:00:00.000 +0200          Clear        rain   \n",
      "8062   2010-08-02 22:00:00.000 +0200  Partly Cloudy        rain   \n",
      "8063   2010-08-02 23:00:00.000 +0200          Clear        rain   \n",
      "36072  2010-08-02 00:00:00.000 +0200          Clear        rain   \n",
      "36073  2010-08-02 01:00:00.000 +0200          Clear        rain   \n",
      "36074  2010-08-02 02:00:00.000 +0200          Clear        rain   \n",
      "36075  2010-08-02 03:00:00.000 +0200          Clear        rain   \n",
      "36076  2010-08-02 04:00:00.000 +0200          Clear        rain   \n",
      "36077  2010-08-02 05:00:00.000 +0200          Clear        rain   \n",
      "36078  2010-08-02 06:00:00.000 +0200          Clear        rain   \n",
      "36079  2010-08-02 07:00:00.000 +0200          Clear        rain   \n",
      "36080  2010-08-02 08:00:00.000 +0200          Clear        rain   \n",
      "36081  2010-08-02 09:00:00.000 +0200          Clear        rain   \n",
      "36082  2010-08-02 10:00:00.000 +0200          Clear        rain   \n",
      "36083  2010-08-02 11:00:00.000 +0200          Clear        rain   \n",
      "36084  2010-08-02 12:00:00.000 +0200          Clear        rain   \n",
      "36085  2010-08-02 13:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36086  2010-08-02 14:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36087  2010-08-02 15:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36088  2010-08-02 16:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36089  2010-08-02 17:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36090  2010-08-02 18:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36091  2010-08-02 19:00:00.000 +0200          Clear        rain   \n",
      "36092  2010-08-02 20:00:00.000 +0200          Clear        rain   \n",
      "36093  2010-08-02 21:00:00.000 +0200          Clear        rain   \n",
      "36094  2010-08-02 22:00:00.000 +0200  Partly Cloudy        rain   \n",
      "36095  2010-08-02 23:00:00.000 +0200          Clear        rain   \n",
      "\n",
      "       Temperature (C)  Apparent Temperature (C)  Humidity  Wind Speed (km/h)  \\\n",
      "8040         18.800000                 18.800000      0.93             6.2790   \n",
      "8041         18.222222                 18.222222      0.97             6.2790   \n",
      "8042         18.072222                 18.072222      0.98            11.2700   \n",
      "8043         16.622222                 16.622222      0.99             6.4400   \n",
      "8044         16.094444                 16.094444      0.99             3.0751   \n",
      "8045         15.955556                 15.955556      0.99             3.8801   \n",
      "8046         17.088889                 17.088889      1.00             6.4400   \n",
      "8047         20.822222                 20.822222      0.87             3.2200   \n",
      "8048         23.405556                 23.405556      0.74             1.8837   \n",
      "8049         26.050000                 26.050000      0.59             1.5939   \n",
      "8050         27.688889                 28.077778      0.50             0.2254   \n",
      "8051         28.561111                 29.588889      0.54             3.2039   \n",
      "8052         28.816667                 29.338889      0.49             1.6100   \n",
      "8053         28.866667                 29.044444      0.46             2.8175   \n",
      "8054         29.827778                 30.338889      0.47             8.0339   \n",
      "8055         30.072222                 30.527778      0.46             2.5921   \n",
      "8056         31.066667                 31.627778      0.44             1.3846   \n",
      "8057         30.861111                 31.361111      0.44             2.2540   \n",
      "8058         29.950000                 30.416667      0.46             3.0751   \n",
      "8059         28.811111                 30.616667      0.59             3.2039   \n",
      "8060         25.250000                 25.250000      0.75             2.4955   \n",
      "8061         22.172222                 22.172222      0.87             1.6100   \n",
      "8062         21.061111                 21.061111      0.90             0.0000   \n",
      "8063         20.255556                 20.255556      0.92             1.0787   \n",
      "36072        18.800000                 18.800000      0.93             6.2790   \n",
      "36073        18.222222                 18.222222      0.97             6.2790   \n",
      "36074        18.072222                 18.072222      0.98            11.2700   \n",
      "36075        16.622222                 16.622222      0.99             6.4400   \n",
      "36076        16.094444                 16.094444      0.99             3.0751   \n",
      "36077        15.955556                 15.955556      0.99             3.8801   \n",
      "36078        17.088889                 17.088889      1.00             6.4400   \n",
      "36079        20.822222                 20.822222      0.87             3.2200   \n",
      "36080        23.405556                 23.405556      0.74             1.8837   \n",
      "36081        26.050000                 26.050000      0.59             1.5939   \n",
      "36082        27.688889                 28.077778      0.50             0.2254   \n",
      "36083        28.561111                 29.588889      0.54             3.2039   \n",
      "36084        28.816667                 29.338889      0.49             1.6100   \n",
      "36085        28.866667                 29.044444      0.46             2.8175   \n",
      "36086        29.827778                 30.338889      0.47             8.0339   \n",
      "36087        30.072222                 30.527778      0.46             2.5921   \n",
      "36088        31.066667                 31.627778      0.44             1.3846   \n",
      "36089        30.861111                 31.361111      0.44             2.2540   \n",
      "36090        29.950000                 30.416667      0.46             3.0751   \n",
      "36091        28.811111                 30.616667      0.59             3.2039   \n",
      "36092        25.250000                 25.250000      0.75             2.4955   \n",
      "36093        22.172222                 22.172222      0.87             1.6100   \n",
      "36094        21.061111                 21.061111      0.90             0.0000   \n",
      "36095        20.255556                 20.255556      0.92             1.0787   \n",
      "\n",
      "       Wind Bearing (degrees)  Visibility (km)  Pressure (millibars)  \\\n",
      "8040                    270.0          14.9086               1016.99   \n",
      "8041                    291.0          14.9086               1017.09   \n",
      "8042                    290.0           6.8425               1013.23   \n",
      "8043                    300.0          11.9784               1016.78   \n",
      "8044                    280.0          11.9784               1016.67   \n",
      "8045                    276.0           9.9820               1016.69   \n",
      "8046                    310.0           9.9820               1017.08   \n",
      "8047                    300.0           9.9820               1017.27   \n",
      "8048                    334.0           9.9820               1017.27   \n",
      "8049                      5.0           9.9820               1017.15   \n",
      "8050                    338.0           9.9820               1016.68   \n",
      "8051                    288.0          10.3523               1016.58   \n",
      "8052                     20.0           9.9820               1016.55   \n",
      "8053                     33.0           9.9820               1015.99   \n",
      "8054                    267.0          10.3523               1015.50   \n",
      "8055                     92.0           9.9820               1014.97   \n",
      "8056                    297.0           9.9820               1014.47   \n",
      "8057                    325.0          10.3523               1014.08   \n",
      "8058                    303.0           9.9820               1013.59   \n",
      "8059                    351.0           9.9820               1013.19   \n",
      "8060                    335.0          10.3523               1013.20   \n",
      "8061                      0.0           9.9820               1013.49   \n",
      "8062                      0.0          11.9784               1013.37   \n",
      "8063                    326.0          14.1680               1013.00   \n",
      "36072                   270.0          14.9086               1016.99   \n",
      "36073                   291.0          14.9086               1017.09   \n",
      "36074                   290.0           6.8425               1013.23   \n",
      "36075                   300.0          11.9784               1016.78   \n",
      "36076                   280.0          11.9784               1016.67   \n",
      "36077                   276.0           9.9820               1016.69   \n",
      "36078                   310.0           9.9820               1017.08   \n",
      "36079                   300.0           9.9820               1017.27   \n",
      "36080                   334.0           9.9820               1017.27   \n",
      "36081                     5.0           9.9820               1017.15   \n",
      "36082                   338.0           9.9820               1016.68   \n",
      "36083                   288.0          10.3523               1016.58   \n",
      "36084                    20.0           9.9820               1016.55   \n",
      "36085                    33.0           9.9820               1015.99   \n",
      "36086                   267.0          10.3523               1015.50   \n",
      "36087                    92.0           9.9820               1014.97   \n",
      "36088                   297.0           9.9820               1014.47   \n",
      "36089                   325.0          10.3523               1014.08   \n",
      "36090                   303.0           9.9820               1013.59   \n",
      "36091                   351.0           9.9820               1013.19   \n",
      "36092                   335.0          10.3523               1013.20   \n",
      "36093                     0.0           9.9820               1013.49   \n",
      "36094                     0.0          11.9784               1013.37   \n",
      "36095                   326.0          14.1680               1013.00   \n",
      "\n",
      "                                           Daily Summary  \n",
      "8040   Partly cloudy starting in the afternoon contin...  \n",
      "8041   Partly cloudy starting in the afternoon contin...  \n",
      "8042   Partly cloudy starting in the afternoon contin...  \n",
      "8043   Partly cloudy starting in the afternoon contin...  \n",
      "8044   Partly cloudy starting in the afternoon contin...  \n",
      "8045   Partly cloudy starting in the afternoon contin...  \n",
      "8046   Partly cloudy starting in the afternoon contin...  \n",
      "8047   Partly cloudy starting in the afternoon contin...  \n",
      "8048   Partly cloudy starting in the afternoon contin...  \n",
      "8049   Partly cloudy starting in the afternoon contin...  \n",
      "8050   Partly cloudy starting in the afternoon contin...  \n",
      "8051   Partly cloudy starting in the afternoon contin...  \n",
      "8052   Partly cloudy starting in the afternoon contin...  \n",
      "8053   Partly cloudy starting in the afternoon contin...  \n",
      "8054   Partly cloudy starting in the afternoon contin...  \n",
      "8055   Partly cloudy starting in the afternoon contin...  \n",
      "8056   Partly cloudy starting in the afternoon contin...  \n",
      "8057   Partly cloudy starting in the afternoon contin...  \n",
      "8058   Partly cloudy starting in the afternoon contin...  \n",
      "8059   Partly cloudy starting in the afternoon contin...  \n",
      "8060   Partly cloudy starting in the afternoon contin...  \n",
      "8061   Partly cloudy starting in the afternoon contin...  \n",
      "8062   Partly cloudy starting in the afternoon contin...  \n",
      "8063   Partly cloudy starting in the afternoon contin...  \n",
      "36072  Partly cloudy starting in the afternoon contin...  \n",
      "36073  Partly cloudy starting in the afternoon contin...  \n",
      "36074  Partly cloudy starting in the afternoon contin...  \n",
      "36075  Partly cloudy starting in the afternoon contin...  \n",
      "36076  Partly cloudy starting in the afternoon contin...  \n",
      "36077  Partly cloudy starting in the afternoon contin...  \n",
      "36078  Partly cloudy starting in the afternoon contin...  \n",
      "36079  Partly cloudy starting in the afternoon contin...  \n",
      "36080  Partly cloudy starting in the afternoon contin...  \n",
      "36081  Partly cloudy starting in the afternoon contin...  \n",
      "36082  Partly cloudy starting in the afternoon contin...  \n",
      "36083  Partly cloudy starting in the afternoon contin...  \n",
      "36084  Partly cloudy starting in the afternoon contin...  \n",
      "36085  Partly cloudy starting in the afternoon contin...  \n",
      "36086  Partly cloudy starting in the afternoon contin...  \n",
      "36087  Partly cloudy starting in the afternoon contin...  \n",
      "36088  Partly cloudy starting in the afternoon contin...  \n",
      "36089  Partly cloudy starting in the afternoon contin...  \n",
      "36090  Partly cloudy starting in the afternoon contin...  \n",
      "36091  Partly cloudy starting in the afternoon contin...  \n",
      "36092  Partly cloudy starting in the afternoon contin...  \n",
      "36093  Partly cloudy starting in the afternoon contin...  \n",
      "36094  Partly cloudy starting in the afternoon contin...  \n",
      "36095  Partly cloudy starting in the afternoon contin...  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 CSV 文件\n",
    "df = pd.read_csv('weatherHistory.csv')\n",
    "\n",
    "# 查找重复的 Formatted Date\n",
    "duplicate_rows = df[df['Formatted Date'].duplicated(keep=False)]\n",
    "\n",
    "# 检查是否存在重复值并打印出来\n",
    "if not duplicate_rows.empty:\n",
    "    print(f\"发现 {len(duplicate_rows)} 行重复的日期时间记录：\")\n",
    "    print(duplicate_rows)\n",
    "else:\n",
    "    print(\"没有发现重复的日期时间记录。\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Formatted Date（日期时间）确实是有重复数据，但是在保留了第一次出现的数据之后，还需要把数据按照时间顺序排序。\n",
    "先去除 Formatted Date 列中的重复数据（保留首次出现的记录），然后根据 Formatted Date 进行排序。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "处理完成，已保存为 'weatherHistory_adjusted.csv'\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 CSV 文件\n",
    "df = pd.read_csv('weatherHistory.csv')\n",
    "\n",
    "# 将 'Formatted Date' 列转换为 UTC datetime 类型\n",
    "df['Formatted Date'] = pd.to_datetime(df['Formatted Date'], utc=True)\n",
    "\n",
    "# 将时间转换为 UTC+1\n",
    "df['Formatted Date'] = df['Formatted Date'] + pd.Timedelta(hours=1)\n",
    "\n",
    "# 将时间转换回带时区的字符串格式\n",
    "df['Formatted Date'] = df['Formatted Date'].dt.strftime('%Y-%m-%d %H:%M:%S') + '+01:00'\n",
    "\n",
    "# 去重 'Formatted Date' 列\n",
    "df = df.drop_duplicates(subset=['Formatted Date'])\n",
    "\n",
    "# 保存修改后的数据到 CSV 文件\n",
    "df.to_csv('weatherHistory.csv', index=False)\n",
    "\n",
    "print(\"处理完成，已保存为 'weatherHistory.csv'\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "值得注意的是，在某些地区，冬季和夏季时间\n",
    "会有所不同，例如：\n",
    "一些使用+01:00的国家在夏季时可能会调整到+02:00（例如，欧洲的夏时制）。这种情况下，在夏季某些地区的时间可能会被认为相同（例如，冬季的+01:00变为+02:00)，但在标准时间上它们是不同的。\n",
    "例如，欧洲中部时间（Central European Time, CET）是一个典型的例子。\n",
    "我们将把时间数据统一转换为标准时区 UTC+01:00，即欧洲中部标准时间（CET），不再考虑夏令时的变动。这样做有助于确保数据分析时的时区一致性。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
